﻿/*
文     件      名：Datatable2Excel.cs
开     发      者：胡春东
单             位：上海因致信息科技有限公司
时             间：2014-04-16
版             本：V1.0
修订人：		
修订时间：		
实现目标说明：多个datatable导出至多个sheet，并处理超出65536行数据为多个子sheet
调用该方法需传入当前服务器的根目录地址，可由
string sPath = Server.MapPath("~"); 获取
本方法许删除文件夹，可能引起sessio，可以在调用页面的page_load方法中加入下面代码解决

            System.Reflection.PropertyInfo p = typeof(System.Web.HttpRuntime).GetProperty("FileChangesMonitor", System.Reflection.BindingFlags.NonPublic | System.Reflection.BindingFlags.Public | System.Reflection.BindingFlags.Static);
            object o = p.GetValue(null, null);
            System.Reflection.FieldInfo f = o.GetType().GetField("_dirMonSubdirs", System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.NonPublic | System.Reflection.BindingFlags.IgnoreCase);
            object monitor = f.GetValue(o);
            System.Reflection.MethodInfo m = monitor.GetType().GetMethod("StopMonitoring", System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.NonPublic);
            m.Invoke(monitor, new object[] { });

*/
using System;
using System.Collections.Generic;
using System.Text;
using System.IO;
using System.Web;
using System.Data;

using NPOI.HSSF.UserModel;
using NPOI.HPSF;
using NPOI.POIFS.FileSystem;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
//using NPOI.XSSF.UserModel;


namespace Datatable2Excel
{
    public class Datatable2Excel
    {
        //初始化 工作簿
        static HSSFWorkbook workbookExample;
        /// <summary>
        /// DataSet 输出为Excel
        /// </summary>
        /// <param name="export_name">输出Excel文件名</param>
        /// <param name="ds">数据源（DataSet）</param>
        /// <param name="sheet_name">sheet名称数组，包含生成sheet的名称</param>
        /// <param name="isColumnWritten">是否需要导入Datatable的列名</param>
        /// <returns>生成的Excel的绝对路径</returns>
        /// 下载完成请调用删除方法进行删除
        public static string ModifExcel(string basePath, string export_name, DataSet ds, string[] sheet_name, bool isColumnWritten)
        {
            InitializeWorkbook(basePath);

            try
            {
                string path_del = basePath + @"\excel\output";
                if (Directory.Exists(path_del))
                {
                    Directory.Delete(path_del, true);
                }
                Directory.CreateDirectory(path_del);
            }
            catch { }

            string fileName = export_name;//客户端保存的文件名 
            string filePath = basePath + @"\excel\output\" + fileName;
            /// 若处理列名，则每页65535条数据，若不处理，则每页65536条数据
            /// 变量 65536 - RowNum

            int RowNum = 0;
            if (isColumnWritten == true)
            {
                RowNum = 1;
            }
            int active_sheet = 0;
            for (int dt_count = 0; dt_count < ds.Tables.Count; dt_count++)
            {
                DataTable dt = ds.Tables[dt_count].Copy();
                for (int sheet_count = 0; sheet_count < (dt.Rows.Count / (65536 - RowNum)) + 1; sheet_count++)
                {
                    ISheet sheetExample = workbookExample.GetSheetAt(active_sheet);
                    sheetExample.ForceFormulaRecalculation = true;
                    workbookExample.SetSheetName(active_sheet, sheet_name[dt_count].ToString() + (sheet_count + 1).ToString());
                    //列名赋值

                    if (RowNum == 1)
                    {
                        IRow ColumnRow = sheetExample.CreateRow(RowNum);
                        for (int i = 0; i < dt.Columns.Count; i++)
                        {
                            sheetExample.GetRow(0).CreateCell(i).SetCellValue(dt.Columns[i].ColumnName);
                        }

                    }

                    for (int RowIndex = sheet_count * (65536 - RowNum); RowIndex < dt.Rows.Count; RowIndex++)
                    {
                        if ((RowIndex - sheet_count * (65536 - RowNum) + RowNum) == 65536)
                        {
                            break;
                        }
                        IRow row = sheetExample.CreateRow(RowIndex - sheet_count * (65536 - RowNum) + RowNum);
                        for (int CellIndex = 0; CellIndex < dt.Columns.Count; CellIndex++)
                        {
                            row.CreateCell(CellIndex).SetCellValue(dt.Rows[RowIndex][CellIndex].ToString());
                        }
                    }

                    if ((dt_count != ds.Tables.Count - 1) || (sheet_count != (dt.Rows.Count / (65536 - RowNum))))
                    {
                        workbookExample.CreateSheet(active_sheet.ToString());
                    }
                    active_sheet++;
                }
            }

            //如果服务器存在同名文件，则删除
            if (File.Exists(filePath))
            {
                try
                {
                    File.Delete(filePath);
                }
                catch
                {
                    string tempfileName = fileName;
                    while (File.Exists(filePath))
                    {
                        tempfileName = "0" + tempfileName;
                        filePath = basePath + @"\excel\output\" + tempfileName;
                    }
                }
            }

            //关闭文件，并且另存为，路径为 filePath
            FileStream file = new FileStream(filePath, FileMode.Create);
            workbookExample.Write(file);
            file.Close();

            return filePath;
        }

        public static void deleteExcel(string delPath)
        {
            try
            {
                File.Delete(delPath);
            }
            catch { }
        }

        private static void InitializeWorkbook(string basePath)
        {
            string path = AppDomain.CurrentDomain.BaseDirectory;
            string pathdemo = basePath + @"\excel\demo\demo.xls";
            FileStream file = new FileStream(pathdemo, FileMode.OpenOrCreate, FileAccess.ReadWrite);
            workbookExample = new HSSFWorkbook(file);
        }
    }
}
